﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DTO;
using System.Data.SqlClient;


namespace DAO
{
    public class AConnnection
    {
        public static SqlConnection Connect()
        {
            SqlConnection cnn = new SqlConnection(@"Data Source=TRANTHANH-PC\SQLEXPRESS;Initial Catalog=BanSach;Integrated Security=True");
            return cnn;
        }
    }
    public class KhachHang_DAO
    {
        //load danh sach KhachHang:
        public static DataTable LoadDSKhachHang()
        {
            SqlConnection cnn = AConnnection.Connect();
            SqlCommand cmd = new SqlCommand("SelectAll_KhachHang", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
        //Them Khach Hang Moi:
        public static void ThemKhachHang(KhachHang_DTO kh)
        {
            SqlConnection cnn = AConnnection.Connect();
            SqlCommand cmd = new SqlCommand("insert_KhachHang", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@maKH", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@tenKH", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@DiaChi", SqlDbType.NVarChar, 50);
            cmd.Parameters.Add("@SDT", SqlDbType.Char, 15);
            cmd.Parameters.Add("@SoLuongmua", SqlDbType.Int);          
            //gan:
            cmd.Parameters["@maKH"].Value = kh.MaKH;
            cmd.Parameters["@tenKH"].Value = kh.TenKH;
            cmd.Parameters["@DiaChi"].Value = kh.DiaChi;
            cmd.Parameters["@SDT"].Value = kh.SDT;
            cmd.Parameters["@SoLuongmua"].Value = kh.SoLuongMua;
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
       
        //Xoa khach hang:
        public static void XoaKhachHang(string makh)
        {
            SqlConnection cnn = AConnnection.Connect();
            SqlCommand cmd = new SqlCommand("[Delete_KhachHang]", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@MaKH", SqlDbType.NChar, 10);
            cmd.Parameters["@MaKH"].Value = makh;
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
        //Tim ten KH:
        public static DataTable TimTenKH(string ten)
        {
            SqlConnection cnn = AConnnection.Connect();
            SqlCommand cmd = new SqlCommand("Search_KhachHang", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@code", SqlDbType.NVarChar, 50);
            cmd.Parameters["@code"].Value = ten;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
    }
}
